﻿using Dapper;
using System;
using BCEntity.Trading.SaleContract;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.TradingEnums;
using BCCommon.Extensions;

namespace BCData.Trading.SaleContract
{
    /// <summary>
    /// 销售合同
    /// </summary>
    public class SaleContractData : ISaleContractData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM salecontract";
        private const string QUERY = @"SELECT SaleContractId,DeliveryMode,CreateTime,PaymentMode,HasDepositPaid,HasLastPaid,DepositPaymentNo,LastPaymentNo,FullPaymentNo,DoneContractTime,
                                            DoneContractRemark,SaleContractNo,CloseContractTime,CloseContractRemark,CancelContractTime,CancelContractRemark,ContractContent,SaleAmount,
                                            UserId,Consignee,ConsigneePhone,ShippingAddress,DeliveryDate,SaleContractStatus,SaleContractStatusStr
                                        FROM salecontract";
        public SaleContractData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #region 检查销售合同是否存在
        public async Task<bool> Exists(long saleContractId)
        {
            string sql = string.Format(@"{0} WHERE SaleContractId=?SaleContractId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", saleContractId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        public async Task<bool> Exists(string saleContractNo)
        {
            string sql = string.Format(@"{0} WHERE SaleContractNo=?SaleContractNo", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractNo", saleContractNo, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 添加销售合同
        public async Task<SaleContractEntity> Add(SaleContractEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("salecontract", new string[] { "SaleContractNo", "ContractContent", "SaleAmount", "UserId", "Consignee", "ConsigneePhone", "ShippingAddress" });
            sql = string.Format("{0} {1} WHERE SaleContractId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractNo", entity.SaleContractNo?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?ContractContent", entity.ContractContent, DbType.String);
            parameters.Add("?SaleAmount", entity.SaleAmount, DbType.Decimal, size: 16);
            parameters.Add("?UserId", entity.UserId, DbType.Int64, size: 20);
            parameters.Add("?Consignee", entity.Consignee?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ConsigneePhone", entity.ConsigneePhone?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?ShippingAddress", entity.ShippingAddress?.Trim(), DbType.AnsiString, size: 200);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleContractEntity>(sql, parameters, databaseContext.Transaction);

        }
        #endregion

        #region 修改销售合同支付方式
        public async Task<SaleContractEntity> UpdatePaymentMode(SaleContractEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("salecontract", new string[] { "DeliveryMode", "PaymentMode", "DeliveryDate" }, new string[] { "SaleContractId" });
            sql = string.Format("{0} {1} WHERE SaleContractId=?SaleContractId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", entity.SaleContractId, DbType.Int64, size: 20);
            parameters.Add("?DeliveryMode", entity.DeliveryMode?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?PaymentMode", entity.PaymentMode.ToString(), DbType.AnsiString, size: 30);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleContractEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改销售合同支付信息（定金）
        public async Task<SaleContractEntity> UpdatePaymentInfoToDeposit(SaleContractEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("salecontract", new string[] { "HasDepositPaid", "DepositPaymentNo", "SaleContractStatus", "SaleContractStatusStr" }, new string[] { "SaleContractId" });
            sql = string.Format("{0} {1} WHERE SaleContractId=?SaleContractId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", entity.SaleContractId, DbType.Int64, size: 20);
            parameters.Add("?HasDepositPaid", true, DbType.Boolean);
            parameters.Add("?SaleContractStatus", SaleContractStatusType.Processing.ToString(), DbType.AnsiString);
            parameters.Add("?SaleContractStatusStr", SaleContractStatusType.Processing.GetDisplayString(), DbType.AnsiString);
            parameters.Add("?DepositPaymentNo", entity.DepositPaymentNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleContractEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改销售合同支付信息（尾款）
        public async Task<SaleContractEntity> UpdatePaymentInfoToLast(SaleContractEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("salecontract", new string[] {  "HasLastPaid", "LastPaymentNo" }, new string[] { "SaleContractId" });
            sql = string.Format("{0} {1} WHERE SaleContractId=?SaleContractId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", entity.SaleContractId, DbType.Int64, size: 20);
            parameters.Add("?HasLastPaid", true, DbType.Boolean);
            parameters.Add("?LastPaymentNo", entity.LastPaymentNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleContractEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改销售合同支付信息（全款）
        public async Task<SaleContractEntity> UpdatePaymentInfo(SaleContractEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("salecontract", new string[] { "HasDepositPaid", "HasLastPaid", "FullPaymentNo" , "SaleContractStatus", "SaleContractStatusStr" }, new string[] { "SaleContractId" });
            sql = string.Format("{0} {1} WHERE SaleContractId=?SaleContractId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", entity.SaleContractId, DbType.Int64, size: 20);
            parameters.Add("?HasDepositPaid", true, DbType.Boolean);
            parameters.Add("?HasLastPaid", true, DbType.Boolean);
            parameters.Add("?SaleContractStatus", SaleContractStatusType.Processing.ToString(), DbType.AnsiString);
            parameters.Add("?SaleContractStatusStr", SaleContractStatusType.Processing.GetDisplayString(), DbType.AnsiString);
            parameters.Add("?FullPaymentNo", entity.FullPaymentNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleContractEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改销售合同
        public async Task<SaleContractEntity> Update(SaleContractEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("salecontract", new string[] { "DeliveryMode", "PaymentMode", "HasDepositPaid", "HasLastPaid", "DepositPaymentNo", "LastPaymentNo",
                "FullPaymentNo", "DoneContractTime", "DoneContractRemark", "SaleContractNo", "CloseContractTime", "CloseContractRemark", "CancelContractTime", "CancelContractRemark",
                "ContractContent", "SaleAmount", "UserId", "Consignee", "ConsigneePhone", "ShippingAddress", "DeliveryDate" }, new string[] { "SaleContractId" });
            sql = string.Format("{0} {1} WHERE SaleContractId=?SaleContractId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", entity.SaleContractId, DbType.Int64, size: 20);
            parameters.Add("?DeliveryMode", entity.DeliveryMode?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?PaymentMode", entity.PaymentMode.ToString(), DbType.AnsiString, size: 30);
            parameters.Add("?HasDepositPaid", entity.HasDepositPaid, DbType.Boolean);
            parameters.Add("?HasLastPaid", entity.HasLastPaid, DbType.Boolean);
            parameters.Add("?DepositPaymentNo", entity.DepositPaymentNo?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?LastPaymentNo", entity.LastPaymentNo?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?FullPaymentNo", entity.FullPaymentNo?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DoneContractTime", entity.DoneContractTime, DbType.DateTime);
            parameters.Add("?DoneContractRemark", entity.DoneContractRemark?.Trim(), DbType.String);
            parameters.Add("?SaleContractNo", entity.SaleContractNo?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?CloseContractTime", entity.CloseContractTime, DbType.DateTime);
            parameters.Add("?CloseContractRemark", entity.CloseContractRemark?.Trim(), DbType.String);
            parameters.Add("?CancelContractTime", entity.CancelContractTime, DbType.DateTime);
            parameters.Add("?CancelContractRemark", entity.CancelContractRemark?.Trim(), DbType.String);
            parameters.Add("?ContractContent", entity.ContractContent, DbType.String);
            parameters.Add("?SaleAmount", entity.SaleAmount, DbType.Decimal, size: 16);
            parameters.Add("?UserId", entity.UserId, DbType.Int64, size: 20);
            parameters.Add("?Consignee", entity.Consignee?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ConsigneePhone", entity.ConsigneePhone?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?ShippingAddress", entity.ShippingAddress?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?DeliveryDate", entity.DeliveryDate, DbType.DateTime);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<SaleContractEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 删除销售合同
        public async Task<bool> Delete(long saleContractId)
        {
            string sql = @"DELETE FROM salecontract WHERE SaleContractId=?SaleContractId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", saleContractId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取销售合同
        public async Task<SaleContractEntity> GetEntity(long saleContractId)
        {
            string sql = string.Format(@"{0} WHERE SaleContractId=?SaleContractId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractId", saleContractId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<SaleContractEntity>(sql, parameters);
        }
        #endregion

        #region 获取销售合同列表
        public async Task<IEnumerable<SaleContractEntity>> GetList(long userId)
        {
            string sql = string.Format(@"{0} WHERE UserId=?UserId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<SaleContractEntity>(sql, parameters);
        }
        #endregion

        #region 获取销售合同分页
        public async Task<(IEnumerable<SaleContractEntity>, int)> Query(long? userId, SaleContractStatusType? saleContractStatus, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (userId.HasValue)
            {
                wheres.Add("UserId=?UserId");
                parameters.Add("?UserId", userId, DbType.Int64);
            }

            if (saleContractStatus.HasValue)
            {
                wheres.Add("SaleContractStatus=?SaleContractStatus)");
                parameters.Add("?SaleContractStatus", saleContractStatus.Value.ToString(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM salecontract {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<SaleContractEntity, int>(sql, parameters);
        }
        #endregion
    }
}



